from fastapi import APIRouter
import pandas as pd
from pydantic import BaseModel

from routes import sqlite
from util.common import select_file

certif_app = APIRouter()


# 添加收件人
@certif_app.post("/add_CertificateCode")
async def add_CertificateCode():
    excelname = select_file('请选择excel文件')
    if excelname == '':
        return {
            "code": 204,
            "status": "no",
            "message": "没有选择文件",
            "path": '',
            "data": []
        }
    if sqlite.Execute_one_SQL("DELETE FROM CertificateCode"):
        try:
            df = pd.read_excel(excelname, sheet_name='运输报告', usecols=['Item', 'CertificateCode','battCode','CertifiedModel','prio'],
                               dtype={'Item': str,'prio':int})
            if df.empty:
                return {
                    "code": 204,
                    "status": "no",
                    "message": "没有数据",
                }
            if sqlite.insert_dataframe(df, 'CertificateCode'):
                return {
                    "code": 200,
                    "status": "ok",
                    "message": "添加成功",
                }
            else:
                return {
                    "code": 204,
                    "status": "no",
                    "message": "添加失败",
                }
        except Exception as e:
            return {
                "code": 204,
                "status": "no",
                "message": str(e),
            }
    else:
        return {
            "code": 204,
            "status": "no",
            "message": "清空表数据失败",
        }

# @certif_app.get("/query_CertificateCode")
# async def query_CertificateCode(Item: str = "", CertificateCode: str = "",CertifiedModel: str = "", page: int = 1, page_size: int = 50):
#     try:
#         df=pd.DataFrame()
#         if ',' in Item:
#             data_sql = f"""SELECT id,Item, CertificateCode,battCode,CertifiedModel, (SELECT COUNT(*) FROM CertificateCode WHERE Item in({Item})) as total FROM CertificateCode WHERE Item in({Item})"""
#             df = sqlite.query(data_sql)
#         else:
#         #     data_sql = """
#         #         SELECT id,Item, CertificateCode,battCode,CertifiedModel, (SELECT COUNT(*) FROM CertificateCode WHERE Item LIKE ? AND CertificateCode LIKE ? AND CertifiedModel LIKE ?) as total
#         #         FROM CertificateCode
#         #         WHERE Item LIKE ? AND CertificateCode LIKE ? AND CertifiedModel LIKE ?
#         #         LIMIT ? OFFSET ?
#         #         """
#         #     params = (
#         #     '%' + Item + '%', '%' + CertificateCode + '%','%' + CertifiedModel + '%', '%' + Item + '%', '%' + CertificateCode + '%','%' + CertifiedModel + '%', page_size,
#         #     (page - 1) * page_size)
#         #     df = sqlite.query(data_sql, params)
#         # total = int(df.loc[0, 'total'])
#             # 计算满足条件的总记录数
#             count_sql = """
#                     SELECT COUNT(*) AS count_all
#                     FROM CertificateCode
#                     WHERE Item LIKE ? AND CertificateCode LIKE ? AND CertifiedModel LIKE ?
#                 """
#
#             # 查询具体数据
#             data_sql = """
#                     SELECT id, Item, CertificateCode, battCode, CertifiedModel
#                     FROM CertificateCode
#                     WHERE Item LIKE ? AND CertificateCode LIKE ? AND CertifiedModel LIKE ?
#                     LIMIT ? OFFSET ?
#                 """
#
#             params_count = ('%' + Item + '%', '%' + CertificateCode + '%', '%' + CertifiedModel + '%')
#             params_data = params_count + (page_size, (page - 1) * page_size)
#
#             # 执行计数查询
#             df_count = sqlite.query(count_sql, params_count)
#             total = int(df_count.loc[0, 'count_all'])
#
#             # 执行数据查询
#             df = sqlite.query(data_sql, params_data)
#         if df.empty:
#             return {
#                 "code": 204,
#                 "status": "no",
#                 "message": "没有数据",
#                 "total": total,  # 返回总记录数
#                 "data": []
#             }
#
#         # 将DataFrame转换为字典列表
#         dict_list = df.to_dict(orient='records')
#         return {
#             "code": 200,
#             "status": "ok",
#             "message": "success",
#             "total": total,  # 返回总记录数
#             "data": dict_list
#         }
#     except Exception as e:
#         return {
#             "code": 204,
#             "status": "no",
#             "message": str(e),
#         }

# @certif_app.get("/query_CertificateCode")
# async def query_CertificateCode(Item: str = "", CertificateCode: str = "", CertifiedModel: str = "", page: int = 1, page_size: int = 50):
#     try:
#         df = pd.DataFrame()
#
#         # 准备WHERE子句和查询参数
#         where_clauses = []
#         params = []
#
#         # 处理Item参数
#         if Item:
#             items = Item.split(',')
#             placeholders = ','.join(['?' for _ in items])
#             where_clauses.append(f"Item IN ({placeholders})")
#             params.extend(items)
#
#         # 处理CertificateCode参数
#         if CertificateCode:
#             where_clauses.append("CertificateCode LIKE ?")
#             params.append(f"%{CertificateCode}%")
#
#         # 处理CertifiedModel参数
#         if CertifiedModel:
#             where_clauses.append("CertifiedModel LIKE ?")
#             params.append(f"%{CertifiedModel}%")
#
#         # 组合WHERE子句
#         where_clause = " AND ".join(where_clauses)
#         if where_clause:
#             where_clause = "WHERE " + where_clause
#
#         # 计算满足条件的总记录数
#         count_sql = f"""
#             SELECT COUNT(*) AS count_all
#             FROM CertificateCode
#             {where_clause}
#         """
#
#         # 查询具体数据
#         data_sql = f"""
#             SELECT id, Item, CertificateCode, battCode, CertifiedModel
#             FROM CertificateCode
#             {where_clause}
#             LIMIT ? OFFSET ?
#         """
#
#         # 添加 LIMIT 和 OFFSET 的参数
#         params_count = params.copy()
#         params_data = params + [page_size, (page - 1) * page_size]
#
#         # 执行计数查询
#         df_count = sqlite.query(count_sql, params_count)
#         total = int(df_count.loc[0, 'count_all'])
#
#         # 执行数据查询
#         df = sqlite.query(data_sql, params_data)
#
#         if df.empty:
#             return {
#                 "code": 204,
#                 "status": "no",
#                 "message": "没有数据",
#                 "total": total,  # 返回总记录数
#                 "data": []
#             }
#
#         # 将DataFrame转换为字典列表
#         dict_list = df.to_dict(orient='records')
#         return {
#             "code": 200,
#             "status": "ok",
#             "message": "success",
#             "total": total,  # 返回总记录数
#             "data": dict_list
#         }
#     except Exception as e:
#         return {
#             "code": 204,
#             "status": "no",
#             "message": str(e),
#         }
@certif_app.get("/query_CertificateCode")
async def query_CertificateCode(Item: str = "", CertificateCode: str = "", CertifiedModel: str = "", page: int = 1, page_size: int = 50):
    try:
        df = pd.DataFrame()

        # 准备WHERE子句和查询参数
        where_clauses = []
        params = []
        item_=''

        # 处理Item参数
        if Item:
            if ',' in Item:
                # 移除单引号并进行分割
                items = [item.strip("'") for item in Item.split(',')]
                placeholders = ','.join(['?' for _ in items])
                where_clauses.append(f"Item IN ({placeholders})")
                params.extend(items)
            else:
                where_clauses.append(f"Item LIKE ?")
                params.append(f"%{Item}%")
                if '-' in Item:
                    item_=Item.split('-')[0]

        # 处理CertificateCode参数
        if CertificateCode:
            where_clauses.append("CertificateCode LIKE ?")
            params.append(f"%{CertificateCode}%")

        # 处理CertifiedModel参数
        if CertifiedModel:
            where_clauses.append("CertifiedModel LIKE ?")
            params.append(f"%{CertifiedModel}%")

        # 组合WHERE子句
        where_clause = " AND ".join(where_clauses)
        if where_clause:
            where_clause = "WHERE " + where_clause

        # 计算满足条件的总记录数
        count_sql = f"""
            SELECT COUNT(*) AS count_all
            FROM CertificateCode
            {where_clause}
        """

        # 查询具体数据
        data_sql = f"""
            SELECT id, Item, CertificateCode, battCode, CertifiedModel
            FROM CertificateCode
            {where_clause}
            LIMIT ? OFFSET ?
        """

        # 添加 LIMIT 和 OFFSET 的参数
        params_count = params.copy()
        params_data = params + [page_size, (page - 1) * page_size]

        # 执行计数查询
        df_count = sqlite.query(count_sql, params_count)
        total = int(df_count.loc[0, 'count_all'])

        # 执行数据查询
        df = sqlite.query(data_sql, params_data)

        if df.empty:
            if  item_:
                where_clauses=[]
                params=[]
                where_clauses.append(f"Item LIKE ?")
                params.append(f"%{item_}%")
                # 计算满足条件的总记录数
                count_sql = f"""
                            SELECT COUNT(*) AS count_all
                            FROM CertificateCode
                            {where_clause}
                        """

                # 查询具体数据
                data_sql = f"""
                            SELECT id, Item, CertificateCode, battCode, CertifiedModel
                            FROM CertificateCode
                            {where_clause}
                            LIMIT ? OFFSET ?
                        """

                # 添加 LIMIT 和 OFFSET 的参数
                params_count = params.copy()
                params_data = params + [page_size, (page - 1) * page_size]

                # 执行计数查询
                df_count = sqlite.query(count_sql, params_count)
                total = int(df_count.loc[0, 'count_all'])

                # 执行数据查询
                df = sqlite.query(data_sql, params_data)
                if df.empty:
                    return {
                        "code": 204,
                        "status": "no",
                        "message": "没有数据",
                        "total": total,  # 返回总记录数
                        "data": []
                    }
            else:
                return {
                    "code": 204,
                    "status": "no",
                    "message": "没有数据",
                    "total": total,  # 返回总记录数
                    "data": []
                }

        # 将DataFrame转换为字典列表
        dict_list = df.to_dict(orient='records')
        return {
            "code": 200,
            "status": "ok",
            "message": "success",
            "total": total,  # 返回总记录数
            "data": dict_list
        }
    except Exception as e:
        return {
            "code": 204,
            "status": "no",
            "message": str(e),
        }

# 添加或编辑单个收件人
class AddrecipientsType(BaseModel):
    id: int
    Item: str
    CertificateCode: str
    battCode: str=''


@certif_app.post("/add_oneCertificateCode")
async def add_oneCertificateCode(data: AddrecipientsType):
    sql = f'''
          UPDATE CertificateCode SET Item='{data.Item}',CertificateCode='{data.CertificateCode}',battCode='{data.battCode}' 
          WHERE id={data.id};
                  '''
    try:
        if sqlite.Execute_one_SQL(sql):
            return {
                "code": 200,
                "status": "ok",
                "msg": "修改成功",
            }
        else:
            return {
                "code": 204,
                "status": "no",
                "msg": "修改失败",
            }
    except Exception as e:
        return {
            "code": 204,
            "status": "no",
            "msg": str(e),
        }

@certif_app.delete("/del_oneCertificateCode")
async def del_oneCertificateCode(id: str):
    sql = f'''
          DELETE FROM CertificateCode 
          WHERE id='{id}';
              '''
    try:
        if sqlite.Execute_one_SQL(sql):
            return {
                "code": 200,
                "status": "ok",
                "msg": "删除成功",
            }
        else:
            return {
                "code": 204,
                "status": "no",
                "msg": "删除失败",
            }
    except Exception as e:
        return {
            "code": 204,
            "status": "no",
            "msg": str(e),
        }
